package com.flash.javamate;

import com.flash.javamate.utils.ExcelUtil;
import com.flash.javamate.utils.JdbcUtils;
import com.flash.javamate.utils.TableProfile;

import java.io.*;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by sunyiming on 2018/7/20.
 * Description:
 */
public class FuckDbaDemo {

    public static Set<String> tableShort = new HashSet<>();

    //数据库用户名
    private static final String USERNAME = "cloudservice";
    //数据库密码
    private static final String PASSWORD = "nEt53kn@";
    //驱动信息
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    //数据库地址
    private static final String URL = "jdbc:mysql://jy.moviebarcloud.w.qiyi.db:8705/cloudservice";

    public static void main(String[] args) throws Exception {
        generateTableXls();
        //showMeSomeTable();
    }

    public static void generateTableXls() throws Exception {
        JdbcUtils jdbcUtils = new JdbcUtils(USERNAME, PASSWORD, DRIVER, URL);
        List<String> tables = jdbcUtils.getAllTablesLike("%");


        for (String table : tables) {
            if (Character.isUpperCase(table.charAt(0))) {
                continue;
            }
            if (table.startsWith("cloud_")){
                tableShort.add(table.substring(6));
            } else {
                tableShort.add(table);
            }
        }


        for (String table : tables) {
            if (Character.isUpperCase(table.charAt(0))) {
                continue;
            }
            if (!table.endsWith("record")&&!table.endsWith("statistic")&&!table.endsWith("relation")
                    &&!table.endsWith("copy")&&
                    !table.endsWith("log")&&!table.endsWith("history")&&!table.endsWith("config")){
                if (!Character.isUpperCase(table.charAt(0))){
                    if (!table.contains("order")) {
                        List<TableProfile> tableProfiles = jdbcUtils.showTable(table);
                        writeExcel(table + ".xlsx", tableProfiles);
                    }
                }
            }

        }
    }

    public static void showMeSomeTable() throws Exception {
        JdbcUtils jdbcUtils = new JdbcUtils(USERNAME, PASSWORD, DRIVER, URL);
        List<String> tables = jdbcUtils.getAllTablesLike("%");
        Set<String> set = new HashSet<>();
        Iterator<String> iterator = tables.iterator();
        while (iterator.hasNext()) {
            String table = iterator.next();
            if (set.contains(table)){
                System.out.println("去了个重");
                iterator.remove();
            }
            set.add(table);
        }

        for (String table : tables) {
            if (!table.endsWith("record")&&!table.endsWith("statistic")&&!table.endsWith("relation")
                    &&!table.endsWith("copy")&&
                    !table.endsWith("log")&&!table.endsWith("history")&&!table.endsWith("config")){
                if (!Character.isUpperCase(table.charAt(0))){
                    if (!table.contains("order")) {
                        System.out.println(table);
                    }
                }
            }

            if (table.contains("log")) {
                //System.out.println(table);
            }

        }
    }

    public static void writeExcel(String name, List<TableProfile> tableProfiles) throws Exception {

        List<List<String>> lists = new ArrayList<>();
        List<String> head = new ArrayList<>();
        head.add("列名");
        head.add("类型");
        head.add("必填");
        head.add("默认值");
        head.add("备注");
        lists.add(head);
        for (TableProfile tableProfile : tableProfiles) {
            List<String> list = new ArrayList<>();
            list.add(tableProfile.getCOLUMN_NAME());
            list.add(tableProfile.getDATA_TYPE());
            list.add("NO".equals(tableProfile.getIS_NULLABLE()) ? "是" : "否");
            list.add(tableProfile.getCOLUMN_DEFAULT());
            list.add(tableProfile.getCOLUMN_COMMENT());
            lists.add(list);
        }

        //导出文件存放的路径，并且是虚拟目录指向的路径，在web服务器
        String filePath = "tablesXls/";
        //导出文件的前缀
        String filePrefix="";
        //-1表示关闭自动刷新，手动控制写磁盘的时机，其它数据表示多少数据在内存保存，超过的则写入磁盘
        int flushRows=100;

        //指导导出数据的title
        List<String> fieldNames=new ArrayList<String>();
        fieldNames.add("列名");
        fieldNames.add("类型");
        fieldNames.add("必填");
        fieldNames.add("默认值");
        fieldNames.add("备注");

        //告诉导出类数据list中对象的属性，让ExcelExportSXXSSF通过反射获取对象的值
        List<String> fieldCodes=new ArrayList<String>();
        fieldCodes.add("COLUMN_NAME");
        fieldCodes.add("DATA_TYPE");
        fieldCodes.add("IS_NULLABLE");
        fieldCodes.add("COLUMN_DEFAULT");
        fieldCodes.add("COLUMN_COMMENT");

        //注意：fieldCodes和fieldNames个数必须相同且属性和title顺序一一对应，这样title和内容才一一对应


        //开始导出，执行一些workbook及sheet等对象的初始创建
        ExcelUtil excelUtil = ExcelUtil.start(filePath, "/upload/", filePrefix, fieldNames, fieldCodes, flushRows);
        //执行导出
        excelUtil.writeDatasByObject(tableProfiles);
        //输出文件，返回下载文件的http地址
        String webpath = excelUtil.exportFile(name);
        System.out.println(webpath);
    }

    public static void write(String name, List<TableProfile> tableProfiles) throws IOException {
        File file = new File(name);
        // 创建文件
        file.createNewFile();
        // creates a FileWriter Object
        BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "gbk"));

        // 向文件写入内容
        writer.write("列名,类型,必填,默认值,备注");
        writer.newLine();

        for (TableProfile tableProfile : tableProfiles) {
            writer.append(tableProfile.getCOLUMN_NAME()).append(",")
                    .append(tableProfile.getDATA_TYPE()).append(",")
                    .append("NO".equals(tableProfile.getIS_NULLABLE()) ? "是" : "否").append(",")
                    .append(tableProfile.getCOLUMN_DEFAULT()).append(",")
                    .append(tableProfile.getCOLUMN_COMMENT());
            writer.newLine();
            writer.flush();
        }

        writer.flush();
        writer.close();
    }


}
